package dbconnection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

import loja.Cliente;
import loja.Factura;
import loja.JaExisteException;


public class BDcliente {

	private BaseDados base_dados = null;

	public BDcliente(BaseDados bd){
		this.base_dados=bd;

	}

	/***********************************************************
	 * Inserts the Cliente c into the DataBase
	 * @param c Cliente to be inserted in the DataBase
	 * @throws JaExisteException if the Cliente c already exists in the DataBase
	 * @return true if the Cliente c was successfully inserted into the DataBase; false otherwise
	 ***********************************************************/
	protected boolean adiciona(Cliente c) throws JaExisteException
	{

		boolean sucesso = false;
		if(c!=null){
			String tabela="cliente";		
			String query = "insert INTO `" + tabela +"` (`nome`, `nif`, `morada`, `email`) VALUES ('"+c.getNomeCliente()+"', '"+c.getNifCliente()
			+"', '"+c.getMoradaCliente()+"', '"+c.getContactoCliente()+"');" ;
			try {
				base_dados.executeStatement(query);
				sucesso= true;
			} catch (SQLException e) {
				sucesso=false;
			}
		}
		return sucesso;

	}

	/**
	 * Updates the Cliente cliente in the DataBase to match the given fields
	 * @param cliente Cliente to be updated in the DataBase
	 * @param nome	new name for the Cliente cliente
	 * @param nif new nif for the Cliente cliente
	 * @param morada new morada for the Cliente cliente
	 * @param email new email for the Cliente cliente
	 * @return true if the Cliente cliente was successfully updated; false otherwise
	 */
	protected boolean alteraCliente(Cliente cliente, String nome, long nif, String morada,  String email){
		boolean sucesso = false;
		if(nome!=null&&nif>=0&&morada!= null&&email!=null){
			String tabela = "cliente";
			String query = "UPDATE "+tabela+" SET `nome`='"+nome+"', `nif`='"
			+nif+"', `morada`='"+morada+"', `email`='"+email+"' WHERE `idcliente`='"+cliente.getNumeroCliente()+"';";

			try {
				base_dados.executeStatement(query);
				sucesso= true;
			} catch (SQLException e) {
				e.printStackTrace();
				sucesso=false;
			}
		}
		return sucesso;
	}

	/***********************************************************
	 * Indicates whether the Cliente c exists in the DataBase
	 * @param c the Cliente to be searched in the Database
	 * @return true if the Cliente c exists in the DataBase; false otherwise
	 ***********************************************************/

	protected boolean existe(Cliente c){
		if(c!=null){
			try{
				return existe(c.getNumeroCliente());
			}catch(java.lang.NumberFormatException e){
				return false;
			}
		}
		return false;
	}

	/***********************************************************
	 * Returns a Cliente from the DataBase using the id argument as a reference
	 * @param id the integer which will be used to search a Cliente by its id in the DataBase
	 * @return the Cliente in the DataBase whose id is equal to the id argument; null if the id argument is not an id in the Table Cliente in the DataBase;
	 ***********************************************************/
	protected Cliente getCliente(int id) {

		if(existe(id)){
			Cliente c = null;
			String tabela="cliente";
			String coluna = "idcliente";
			String query = "SELECT * FROM  "+tabela+"  WHERE `"+coluna+"` = '"+id+"' ;";
			try {
				ResultSet rs = base_dados.executeStatementQuery(query);
				rs.next();
				c = new Cliente(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getString(5));	

			} catch (SQLException e) {
				e.printStackTrace();
			}
			return c;
		}
		return null;
	}

	/***********************************************************
	 * Returns a Cliente from the DataBase using the Factura f argument as a reference
	 * @param f the Factura which will be used to search a Cliente in the DataBase
	 * @return the Cliente to whom the Factura f is relatedt; null if Fatcura f is not related woth any Cliente;
	 ***********************************************************/
	protected Cliente getCliente(Factura f) {

		if(base_dados.existe(f)){
			Cliente c = null;
			String tabela="cliente";
			String tabela2="`factura`";
			String coluna0 = "`idcliente`";
			String coluna = "`cliente_idcliente`";
			String coluna2 = "idfactura";
			String query = "SELECT * FROM  "+tabela+", "+tabela2+"  WHERE `"+coluna2+"` = '"+f.getNumero()+"' AND "+coluna+" = "+coluna0+";";
			try {
				ResultSet rs = base_dados.executeStatementQuery(query);
				rs.next();
				c = new Cliente(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getString(5));	

			} catch (SQLException e) {
				e.printStackTrace();
			}
			return c;
		}
		return null;
	}
	
	/***********************************************************
	 * Returns all Clientes present in the DataBase
	 * @return the LinkedList<Cliente> which contains all Clientes in the DataBase; null if the DataBase has no Clientes;
	 ***********************************************************/
	protected LinkedList<Cliente> getClientes(){

		if(naoVazia()){		
			LinkedList<Cliente> lista_clientes = null;
			String tabela="cliente";
			String query = "SELECT * FROM " + tabela +";";
			try {
				ResultSet rs = base_dados.executeStatementQuery(query);
				lista_clientes = getClientes(rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}

			return lista_clientes;
		}
		return null;
	}

	/***********************************************************
	 * Removes the Cliente c from the DataBase
	 * @param c the Cliente to be removed from the Database
	 * @return true if the Cliente c was correctly removed from the DataBase; false otherwise
	 ***********************************************************/
	protected boolean retira(Cliente c)
	{
		boolean sucesso = false;
		if(existe(c)){
			String coluna ="idcliente";
			String tabela="cliente";
			int id = c.getNumeroCliente();
			String query = "DELETE FROM " + tabela +" WHERE `"+coluna+"` = '"+id+"' ;";
			try {
				base_dados.executeStatement(query);
				sucesso= true;
			} catch (SQLException e) {
				e.printStackTrace();
				sucesso = false;
			}
		}
		return sucesso;
	}

	private boolean naoVazia() {
		String tabela = "cliente";
		boolean existe = false;
		String query = "SELECT COUNT(*) FROM `" + tabela +";";
		try {
			ResultSet rs = base_dados.executeStatementQuery(query);
			rs.next();
			if(rs.getInt(1)>=1)
				existe = true;

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return existe;
	}

	private LinkedList<Cliente> getClientes(ResultSet rs) throws SQLException {

		LinkedList<Cliente> lista_clientes= new LinkedList<Cliente>();
		while (rs.next()){
	//		lista_clientes.add(getCliente(rs.getInt(1)));
//			rs.next();
			lista_clientes.add(new Cliente(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getString(5)));	

		}

		return lista_clientes;
	}

	private boolean existe(int numeroCliente) {
		String tablename = "cliente";
		String column_name = "idcliente";

		boolean existe = false;
		String query = "SELECT COUNT(*) FROM `" +tablename +"` WHERE `"+column_name+"` = '"+numeroCliente+"' ;";
		try {
			ResultSet rs = base_dados.executeStatementQuery(query);
			rs.next();
			if(rs.getInt(1)>=1)
				existe = true;

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return existe;
	}



}
